﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using XMK.Utils;

namespace XMK.Framework.DataAccess
{
    /// <summary>
    /// 数据库实际操作帮助类
    /// </summary>
    public static class DbHelper
    {
        /// <summary>
        /// 执行命令填充DataSet
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfo">Sql命令实例</param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecFullDataSet(DbProvider dbProvider, DbCmdInfo dbCmdInfo)
        {
            IDbConnection conn = dbProvider.CreateConnection();
            conn.ConnectionString = dbProvider.ConnString;
            IDbCommand cmd = null;
            IDbDataAdapter adapter = null;
            DataSet ds = new DataSet();
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = GetCommand(dbProvider, dbCmdInfo, conn);
                adapter = dbProvider.CreateDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// 执行命令填充DataTable
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfo">Sql命令实例</param>
        /// <returns>返回DataTable</returns>
        public static DataTable ExecFullDataTable(DbProvider dbProvider, DbCmdInfo dbCmdInfo)
        {
            DataSet ds = ExecFullDataSet(dbProvider, dbCmdInfo);
            if(ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            return new DataTable();
        }
        /// <summary>
        /// 执行命令并返回受影响的行数
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfo">Sql命令实例</param>
        /// <returns>命令所影响的行数</returns>
        public static int ExecNonQuery(DbProvider dbProvider, DbCmdInfo dbCmdInfo)
        {
            IDbConnection conn = dbProvider.CreateConnection();
            conn.ConnectionString = dbProvider.ConnString;
            IDbCommand cmd = null;

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = GetCommand(dbProvider, dbCmdInfo, conn);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
        }
        /// <summary>
        /// 执行命令并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfo">Sql命令实例</param>
        /// <returns>结果集中第一行的第一列</returns>
        public static object ExecScalar(DbProvider dbProvider, DbCmdInfo dbCmdInfo)
        {
            IDbConnection conn = dbProvider.CreateConnection();
            conn.ConnectionString = dbProvider.ConnString;
            IDbCommand cmd = null;

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd = GetCommand(dbProvider, dbCmdInfo, conn);
                return cmd.ExecuteScalar();
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
        }

        /// <summary>
        /// 返回游标
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="dbCmdInfo"></param>
        /// <returns></returns>
        public static IDataReader ExecDataReader(DbProvider dbProvider, DbCmdInfo dbCmdInfo)
        {
            IDbConnection conn = dbProvider.CreateConnection();
            conn.ConnectionString = dbProvider.ConnString;
            IDbCommand cmd = null;

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd = GetCommand(dbProvider, dbCmdInfo, conn);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);           
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfos">Sql命令实例集合</param>
        /// <returns>事务是否成功</returns>
        public static bool ExecTrans(DbProvider dbProvider, DbCmdInfo[] dbCmdInfos)
        {
            return ExecTrans(dbProvider, dbCmdInfos,  null);
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfos">Sql命令实例集合</param>
        /// <param name="transSqlCmdFun">事务中后续命令需要之前命令结果并填充参数的方法</param>
        /// <returns>事务是否成功</returns>
        public static bool ExecTrans(DbProvider dbProvider, DbCmdInfo[] dbCmdInfos, TransDbCmdFun transSqlCmdFun)
        {
            IDbConnection conn = dbProvider.CreateConnection();
            conn.ConnectionString = dbProvider.ConnString;
            IDbTransaction trans = null;
            bool isRoll = false;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                trans = conn.BeginTransaction();
                for (int i = 0; i < dbCmdInfos.Length; i++)
                {
                    IDbCommand cmd = GetCommand(dbProvider, dbCmdInfos[i], conn);
                    cmd.Transaction = trans;
                    //命令所影响的行数
                    dbCmdInfos[i].ExecuteNonQuery = cmd.ExecuteNonQuery();
                    if (transSqlCmdFun != null && !transSqlCmdFun(i,dbProvider, dbCmdInfos))
                    {
                        isRoll = true;
                        break;
                    }
                }
                if (isRoll) trans.Rollback();
                else
                {
                    trans.Commit();
                }
            }
            catch(Exception ex)
            {
                if(trans != null)
                    trans.Rollback();
                LogHelper.WriteLog(LogType.Error, "事务执行异常：" + ex.ToString());
                throw ex;
            }
            finally
            {
                if (trans != null) trans.Dispose();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
            return !isRoll;
        }
        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="dbCmdInfos"></param>
        /// <returns></returns>
        public static bool ExecDbCmdInfos(DbProvider dbProvider, DbCmdInfo[] dbCmdInfos)
        {
            return ExecDbCmdInfos(dbProvider, dbCmdInfos, null);
        }
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfos">Sql命令实例集合</param>
        /// <param name="transSqlCmdFun">事务中后续命令需要之前命令结果并填充参数的方法</param>
        /// <returns>事务是否成功</returns>
        public static bool ExecDbCmdInfos(DbProvider dbProvider, DbCmdInfo[] dbCmdInfos, TransDbCmdFun transSqlCmdFun)
        {
            IDbConnection conn = dbProvider.CreateConnection();
            conn.ConnectionString = dbProvider.ConnString;
            bool isRoll = false;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                for (int i = 0; i < dbCmdInfos.Length; i++)
                {
                    IDbCommand cmd = GetCommand(dbProvider, dbCmdInfos[i], conn);
                    //命令所影响的行数
                    dbCmdInfos[i].ExecuteNonQuery = cmd.ExecuteNonQuery();
                    if (transSqlCmdFun != null && !transSqlCmdFun(i, dbProvider, dbCmdInfos))
                    {
                        isRoll = true;
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(LogType.Error, "事务执行异常：" + ex.ToString());
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
            }
            return !isRoll;
        }
        #region 私有方法
        /// <summary>
        /// 生成附带参数的数据库命令
        /// </summary>
        /// <param name="dbProvider">数据库驱动实例</param>
        /// <param name="dbCmdInfo">Sql命令实例</param>
        /// <param name="conn">数据库连接</param>
        /// <returns>数据库命令</returns>
        private static IDbCommand GetCommand(DbProvider dbProvider, DbCmdInfo dbCmdInfo, IDbConnection conn)
        {
            IDbCommand cmd = dbProvider.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = dbCmdInfo.SqlText;
            cmd.CommandType = dbCmdInfo.CommandType;
            cmd.CommandTimeout = dbProvider.CommandTimeout;

            //参数填充处理
            dbProvider.DeriveParameters(cmd, dbCmdInfo);            
            return cmd;
        }
        #endregion


    }
}
